package com.dingding.special.order.channel.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.dingding.common.core.base.daox.impl.SQLBaseDaoImpl;
import com.dingding.common.core.util.ToolUtil;
import com.dingding.facade.special.dto.ReqChannelOrderDTO;
import com.dingding.facade.special.dto.ReqChannelOrdersDTO;
import com.dingding.facade.special.dto.ResChannelOrderDTO;
import com.dingding.facade.special.dto.ResChannelOrdersDTO;
import com.dingding.facade.special.dto.ResChannelOrdersListDTO;
import com.dingding.special.order.channel.dao.ChannelOrderDao;
import com.dingding.special.order.channel.sql.ChannelOrderSql;

/**
 * @ClassName: ChannelOrderDaoImpl
 * @Description: 旅行社后台-获取渠道订单
 * @author SUNZHICHENG
 * @date 2016年9月12日 下午7:01:14
 * 
 */
@Repository("channelOrderDao")
public class ChannelOrderDaoImpl implements ChannelOrderDao {

	Logger log = Logger.getLogger(ChannelOrderDaoImpl.class);

	@Resource(name = "SQLBaseDao")
	SQLBaseDaoImpl sqlBaseDaoImpl;

	/**
	 * @Title: getChannelOrderInfoById 
	 * @Description: 根据订单id查询订单详细
	 * @author SUNZHICHENG  
	 * @date  2016年9月12日 下午8:37:26
	 * @since v2.5.0
	 */

	@Transactional(propagation = Propagation.REQUIRED, readOnly = true)
	@Override
	public ResChannelOrderDTO getChannelOrderInfoById(ReqChannelOrderDTO reqChannelOrderDTO) {
		if (reqChannelOrderDTO != null) {

			if (reqChannelOrderDTO.getSpecialOrderId() != null && !reqChannelOrderDTO.getSpecialOrderId().equals("")) {
				try {
					return sqlBaseDaoImpl.sqlToCustomBeanUnique(ResChannelOrderDTO.class,
							ChannelOrderSql.QUERY_TRAVERORDER, reqChannelOrderDTO.getSpecialOrderId(),reqChannelOrderDTO.getSpecialOrderId());
				} catch (Exception e) {
					log.error("查询旅游订单详情失败" + this.getClass() + "getTravelOrderInfo()", e);
				}

			}
		}
		return null;
	}

	/**
	 * 
	 * @Title: getChannelOrderListByCriteria 
	 * @Description: 根据条件查询渠道所有订单(正式表中已完成和已取消的订单)
	 * @author SUNZHICHENG  
	 * @date  2016年9月13日 下午11:02:02
	 * @since v2.5.0
	 */

	@Transactional(propagation = Propagation.REQUIRED, readOnly = true)
	@Override
	public List<ResChannelOrdersDTO> getChannelOrderListByCriteria(ReqChannelOrdersDTO reqChannelOrdersDTO) {

		if (reqChannelOrdersDTO != null) {
			// 请求参数list
			List<Object> params = new ArrayList<Object>();
			
			StringBuffer sql = new StringBuffer(
					" SELECT uo.specialOrderId specialOrderId, uo.startAddress startAddress,uo.endAddress endAddress, "
							+ " date_format(uo.createTime,'%Y-%m-%d %H:%i:%s') createTime,date_format(uo.aboardTime,'%Y-%m-%d %H:%i:%s') aboardTime,date_format(uo.endTime,'%Y-%m-%d %H:%i:%s') endTime,  "
							+ " CASE WHEN uo.orderStatus =1 THEN '待指派' WHEN uo.orderStatus =2 THEN '待上车(已指派)' WHEN uo.orderStatus =3 THEN  '已上车' WHEN uo.orderStatus =4 THEN  '待支付(已到达)' WHEN uo.orderStatus =5 THEN  '已完成' WHEN uo.orderStatus =6 THEN 	'已取消' END orderStatus, "
							+ " ul.userName passengerTel,su.userName driverTel FROM userspecialorder uo LEFT JOIN userLogin ul ON ul.uid = uo.placeOrderUserId LEFT JOIN  specialuserlogin su ON su.suid = uo.grabOrderUserId WHERE uo.publishType = 4  ");

				
				// 组装查询条件
				assemblyQueryCondition(reqChannelOrdersDTO, params, sql);

				sql.append(" order by uo.createTime desc ");

				return sqlBaseDaoImpl.listSqlToCustomBeanByPage(ResChannelOrdersDTO.class, sql.toString(),
						reqChannelOrdersDTO.getPage(), reqChannelOrdersDTO.getLimit(), params.toArray());
			

		}
		return null;
	}

	/**
	 * 
	 * @Title: getChannelOrdertListByCriteria 
	 * @Description: 根据条件查询当前渠道所有订单(临时表表中的订单)
	 * @author SUNZHICHENG  
	 * @date  2016年9月19日 上午11:20:18
	 * @since V2.5.0
	 */
	@Transactional(propagation = Propagation.REQUIRED, readOnly = true)
	@Override
	public List<ResChannelOrdersDTO> getChannelOrdertListByCriteria(ReqChannelOrdersDTO reqChannelOrdersDTO) {

		if (reqChannelOrdersDTO != null) {
			// 请求参数list
			List<Object> params = new ArrayList<Object>();
			
			// 按照查询条件拼接sql
			StringBuffer sql = new StringBuffer(
					" SELECT uo.specialOrderId specialOrderId, uo.startAddress startAddress,uo.endAddress endAddress, "
							+ " date_format(uo.createTime,'%Y-%m-%d %H:%i:%s') createTime,date_format(uo.aboardTime,'%Y-%m-%d %H:%i:%s') aboardTime,date_format(uo.endTime,'%Y-%m-%d %H:%i:%s') endTime,  "
							+ " CASE WHEN uo.orderStatus =1 THEN '待指派' WHEN uo.orderStatus =2 THEN '待上车(已指派)' WHEN uo.orderStatus =3 THEN  '已上车' WHEN uo.orderStatus =4 THEN  '待支付(已到达)' WHEN uo.orderStatus =5 THEN  '已完成' WHEN uo.orderStatus =6 THEN 	'已取消' END orderStatus, "
							+ " ul.userName passengerTel,su.userName driverTel FROM userspecialordert uo LEFT JOIN userLogin ul ON ul.uid = uo.placeOrderUserId LEFT JOIN  specialuserlogin su ON su.suid = uo.grabOrderUserId WHERE uo.publishType = 4  ");
			
			
				// 组装查询条件
				assemblyQueryCondition(reqChannelOrdersDTO, params, sql);

				sql.append(" order by uo.createTime desc ");

				return sqlBaseDaoImpl.listSqlToCustomBeanByPage(ResChannelOrdersDTO.class, sql.toString(),
						reqChannelOrdersDTO.getPage(), reqChannelOrdersDTO.getLimit(), params.toArray());
			

		}
		return null;
	}

	/**
	 * 
	 * @Title: getTotal
	 * @Description: 查询总条数
	 * @param reqChannelOrderDTO
	 * @author SUNZHICHENG
	 * @date 2016年9月13日 下午11:02:02
	 * @since v2.5.0
	 */

	@Override
	@Transactional(propagation = Propagation.REQUIRED, readOnly = true)
	public ResChannelOrdersListDTO getTotal(ReqChannelOrdersDTO reqChannelOrdersDTO) {
		if (reqChannelOrdersDTO != null) {

			// 请求参数list
			List<Object> params = new ArrayList<Object>();
			
			StringBuffer sql = new StringBuffer(
					" SELECT COUNT(1) total   FROM userspecialorder uo LEFT JOIN userLogin ul ON ul.uid = uo.placeOrderUserId LEFT JOIN  specialuserlogin su ON su.suid = uo.grabOrderUserId WHERE uo.publishType = 4  ");
			
					
			// 组装查询条件
			assemblyQueryCondition(reqChannelOrdersDTO, params, sql);

			return sqlBaseDaoImpl
					.listSqlToCustomBean(ResChannelOrdersListDTO.class, sql.toString(), params.toArray()).get(0);

			

		}

		return null;
	}

	/**
	 * 
	 * @Title: getTotalOrdert 
	 * @Description: 查询临时表中的订单总条数
	 * @param reqChannelOrdersDTO
	 * @author SUNZHICHENG  
	 * @date  2016年9月19日 上午11:42:10
	 * @since V2.5.0
	 */
	@Override
	@Transactional(propagation = Propagation.REQUIRED, readOnly = true)
	public ResChannelOrdersListDTO getTotalOrdert(ReqChannelOrdersDTO reqChannelOrdersDTO) {
		if (reqChannelOrdersDTO != null) {

			// 请求参数list
			List<Object> params = new ArrayList<Object>();
			
			StringBuffer sql = new StringBuffer(
					"SELECT COUNT(1) total   FROM userspecialordert uo LEFT JOIN userLogin ul ON ul.uid = uo.placeOrderUserId LEFT JOIN  specialuserlogin su ON su.suid = uo.grabOrderUserId WHERE uo.publishType = 4  ");
			
			
			// 组装查询条件
			assemblyQueryCondition(reqChannelOrdersDTO, params, sql);

			return sqlBaseDaoImpl
					.listSqlToCustomBean(ResChannelOrdersListDTO.class, sql.toString(), params.toArray()).get(0);

			

		}
		return null;
	}

	/**
	 * 
	 * @Title: assemblyQueryCondition 
	 * @Description: 组装查询(查询正式表)
	 * @param reqChannelOrdersDTO
	 * @param params
	 * @param sql
	 * @author SUNZHICHENG  
	 * @date  2016年9月14日 上午10:27:02
	 * @since v2.5.0
	 */
	private void assemblyQueryCondition(ReqChannelOrdersDTO reqChannelOrdersDTO, List<Object> params,
			StringBuffer sql) {
		
		// 渠道id
		if (reqChannelOrdersDTO.getDepId() != null && !reqChannelOrdersDTO.getDepId().equals("")) {
			// 按照查询条件拼接sql
			sql.append(" AND uo.depId = ? ");
			// 添加渠道id
			params.add(reqChannelOrdersDTO.getDepId());
			}		
		
		// 添加查询条件(订单状态)
		if (reqChannelOrdersDTO.getOrderStatus() != null && !reqChannelOrdersDTO.getOrderStatus().equals("")
				&& !reqChannelOrdersDTO.getOrderStatus().equals("null")) {
			sql.append(" AND uo.orderStatus = ? ");
			params.add(reqChannelOrdersDTO.getOrderStatus());
		}

		// 添加查询条件(开始日期)
		if (ToolUtil.isNotBlank(reqChannelOrdersDTO.getStartTime())
				&& !reqChannelOrdersDTO.getStartTime().equals("null")) {
			sql.append(" AND  date_format(uo.endTime,'%Y-%m-%d') >= ?  ");
			params.add(reqChannelOrdersDTO.getStartTime());

		}
		// 添加查询条件(结束日期)
		if (ToolUtil.isNotBlank(reqChannelOrdersDTO.getEndtime()) && !reqChannelOrdersDTO.getEndtime().equals("null")) {
			sql.append(" AND  date_format(uo.endTime,'%Y-%m-%d') <= ? ");
			params.add(reqChannelOrdersDTO.getEndtime());

		}

		/*
		 * //添加查询条件(开始日期、结束日期) if (reqChannelOrdersDTO.getStartTime()!=null &&
		 * !reqChannelOrdersDTO.getStartTime().equals("") &&
		 * reqChannelOrdersDTO.getEndtime() !=null &&
		 * !reqChannelOrdersDTO.getEndtime().equals("")) { sql.append(
		 * " AND date_format(uo.endTime,'%Y-%m-%d') BETWEEN ? and  ? ");
		 * params.add(reqChannelOrdersDTO.getStartTime());
		 * params.add(reqChannelOrdersDTO.getEndtime());
		 * 
		 * }
		 */
		// 添加查询条件(订单id)
		if (reqChannelOrdersDTO.getSpecialOrderId() != null && !reqChannelOrdersDTO.getSpecialOrderId().equals("")) {
			sql.append(" AND uo.specialOrderId = ? ");
			params.add(reqChannelOrdersDTO.getSpecialOrderId());

		}
		// 添加查询条件(乘客手机号)
		if (reqChannelOrdersDTO.getPassengerTel() != null && !reqChannelOrdersDTO.getPassengerTel().equals("")) {
			sql.append(" AND ul.userName = ?  ");
			params.add(reqChannelOrdersDTO.getPassengerTel());

		}
		// 添加查询条件(司机手机号)
		if (reqChannelOrdersDTO.getDriverTel() != null && !reqChannelOrdersDTO.getDriverTel().equals("")) {
			sql.append(" AND su.userName = ? ");
			params.add(reqChannelOrdersDTO.getDriverTel());
		}
	}

	/**
	 * 
	 * @Title: assemblyQueryConditionOrdert 
	 * @Description: 组装查询(查询临时表)
	 * @param reqChannelOrdersDTO
	 * @author SUNZHICHENG  
	 * @date  2016年9月19日 上午11:28:51
	 * @since V2.5.0
	 */
	
	/*private void assemblyQueryConditionOrdert(ReqChannelOrdersDTO reqChannelOrdersDTO, List<Object> params,
			StringBuffer sql) {
		// 添加查询条件(订单状态)
		if (reqChannelOrdersDTO.getOrderStatus() != null && !reqChannelOrdersDTO.getOrderStatus().equals("")
				&& !reqChannelOrdersDTO.getOrderStatus().equals("null")) {
			sql.append(" AND uo.orderStatus = ? ");
			params.add(reqChannelOrdersDTO.getOrderStatus());
		}

		// 添加查询条件(开始日期)
		if (ToolUtil.isNotBlank(reqChannelOrdersDTO.getStartTime())
				&& !reqChannelOrdersDTO.getStartTime().equals("null")) {
			sql.append(" AND  date_format(uo.endTime,'%Y-%m-%d') >= ?  ");
			params.add(reqChannelOrdersDTO.getStartTime());

		}
		// 添加查询条件(结束日期)
		if (ToolUtil.isNotBlank(reqChannelOrdersDTO.getEndtime()) && !reqChannelOrdersDTO.getEndtime().equals("null")) {
			sql.append(" AND  date_format(uo.endTime,'%Y-%m-%d') <= ? ");
			params.add(reqChannelOrdersDTO.getEndtime());

		}

		// 添加查询条件(订单id)
		if (reqChannelOrdersDTO.getSpecialOrderId() != null && !reqChannelOrdersDTO.getSpecialOrderId().equals("")) {
			sql.append(" AND uo.specialOrderId = ? ");
			params.add(reqChannelOrdersDTO.getSpecialOrderId());

		}
		// 添加查询条件(乘客手机号)
		if (reqChannelOrdersDTO.getPassengerTel() != null && !reqChannelOrdersDTO.getPassengerTel().equals("")) {
			sql.append(" AND ul.userName = ?  ");
			params.add(reqChannelOrdersDTO.getPassengerTel());

		}

	}*/

}
